package com.fone.player.storage;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteStatement;
import android.provider.MediaStore;

import com.fone.player.entity.MediaFile;
import com.fone.player.entity.MediaFolder;
import com.fone.player.util.L;

/**
 * 
 * 文件表适配器
 * 
 * @author wangyang
 * @since 2014年4月29日
 */
class FileDataBaseAdapter implements IFoneDatabase {
	private static final String TAG = FileDataBaseAdapter.class.getSimpleName();
	private static final FileDataBaseAdapter mInstance = new FileDataBaseAdapter();
	private OperateDataBaseTemplate mGeneralDataBaseTemplate = OperateDataBaseTemplate
			.getInstance();
	private static Context sContext = null;

	private FileDataBaseAdapter() {

	}

	static FileDataBaseAdapter getInstance() {
		return mInstance;
	}

	static void init(Context context) {
		sContext = context;
	}

	public boolean addFolderList(List<MediaFolder> folderList,
			List<MediaFile> fileList) {
		// 需要更新的集合
		// List<MediaFolder> updateMediaFolderList = new
		// ArrayList<MediaFolder>();
		List<MediaFile> updateMediaFileList = new ArrayList<MediaFile>();
		// 需要添加的集合
		List<MediaFolder> addMediaFolderList = new ArrayList<MediaFolder>();
		List<MediaFile> addMediaFileList = new ArrayList<MediaFile>();
		// 需要删除的集合
		List<String> deleteMediaList = new ArrayList<String>();

		// 临时表
		HashMap<String, Object> mediaPathHashMap = new HashMap<String, Object>();

		mGeneralDataBaseTemplate.open();
		StringBuilder folderListSql = new StringBuilder("select * from ");
		folderListSql.append(TB_DIRECTORY_TREE);
		Cursor cursor = mGeneralDataBaseTemplate.select(folderListSql
				.toString());
		if (cursor != null && cursor.getCount() != 0) {
			while (cursor.moveToNext()) {
				String path = cursor.getString(cursor
						.getColumnIndex(DIRECTORY_PATH));
				if (path != null) {
					File file = new File(path);
					if (file.exists()) {

						mediaPathHashMap.put(path, null);

					} else {
						// 删除
						deleteMediaList.add(path);

					}
				} else {
					// 删除
					deleteMediaList.add(path);
				}

			}
			cursor.close();
		}
		mGeneralDataBaseTemplate.close();

		// 文件夹
		for (MediaFolder mediaFolder : folderList) {
			String path = mediaFolder.getMediaFolderPath();
			if (path != null) {
				File file = new File(path);
				if (file.exists()) {
					if (mediaPathHashMap.containsKey(path)) {
						// 更新(文件夹暂时不更新)
						// updateMediaFolderList.add(mediaFolder);
					} else {
						// 添加
						addMediaFolderList.add(mediaFolder);
					}

				}
			}

		}

		// 文件
		for (MediaFile mediaFile : fileList) {
			String path = mediaFile.getMediaFilePath();
			if (path != null) {
				File file = new File(path);
				if (file.exists()) {
					if (mediaPathHashMap.containsKey(path)) {
						// 更新(文件夹暂时不更新)
						updateMediaFileList.add(mediaFile);
					} else {
						// 添加
						addMediaFileList.add(mediaFile);
					}

				}
			}
		}

		// 添加
		if (addMediaFolderList.size() != 0 || addMediaFileList.size() != 0) {

			addFileList(addMediaFolderList, addMediaFileList);

		}

		// 删除
		if (deleteMediaList.size() != 0) {

			deleteFileList(deleteMediaList);
		}

		// 更新
		if (updateMediaFileList.size() != 0) {

			updateFileList(updateMediaFileList);
		}

		return true;
	}

	/**
	 * 
	 * 添加文件列表
	 * 
	 * @param fileList
	 *            文件集合
	 * @return boolean 是否成功
	 */
	public boolean addFileList(List<MediaFolder> folderList,
			List<MediaFile> fileList) {

		synchronized (TAG) {

			// 创建文件夹sql语句
			StringBuilder folderSql = new StringBuilder();
			folderSql.append("replace into ").append(TB_DIRECTORY_TREE)
					.append(" (");
			folderSql.append(DIRECTORY_NAME).append(",");
			folderSql.append(DIRECTORY_PATH).append(",");
			folderSql.append(DIRECTORY_PARENT_PATH).append(",");
			folderSql.append(DIRECTORY_TYPE).append(",");
			folderSql.append(FILE_IS_ENCRYPT);
			folderSql.append(") values(?,?,?,?,?);");

			// 创建文件sql语句
			StringBuilder fileSql = new StringBuilder();
			fileSql.append("replace into ").append(TB_DIRECTORY_TREE)
					.append(" (");
			fileSql.append(DIRECTORY_NAME).append(",");
			fileSql.append(DIRECTORY_NAME_SUFFIX).append(",");
			fileSql.append(DIRECTORY_PATH).append(",");
			fileSql.append(DIRECTORY_PARENT_PATH).append(",");
			fileSql.append(DIRECTORY_DATE_MODIFIED).append(",");
			fileSql.append(DIRECTORY_AUTHOR_NAME).append(",");
			fileSql.append(DIRECTORY_TYPE).append(",");
			fileSql.append(FILE_ALBUM_NAME).append(",");
			fileSql.append(FILE_DATE_DURATION).append(",");
			fileSql.append(FILE_IS_ENCRYPT).append(",");
			fileSql.append(FILE_SIZE).append(",");
			fileSql.append(FILE_ENCRYPT_TIME);
			fileSql.append(") values(?,?,?,?,?,?,?,?,?,?,?,?);");

			try {
				mGeneralDataBaseTemplate.open();

				mGeneralDataBaseTemplate.beginTransaction();

				// 文件夹
				SQLiteStatement folderStatement = mGeneralDataBaseTemplate
						.getSQLiteStatement(folderSql.toString());
				for (MediaFolder mediaFolder : folderList) {

					folderStatement.bindString(1,
							mediaFolder.getMediaFolderName());
					folderStatement.bindString(2,
							mediaFolder.getMediaFolderPath());
					folderStatement.bindString(3,
							mediaFolder.getMediaFolderParentPath());
					// 文件夹
					folderStatement.bindLong(4, 1);
					// 是否加密
					folderStatement.bindLong(5, 0);
					mGeneralDataBaseTemplate.insert(folderStatement);
				}

				// 文件
				SQLiteStatement fileStatement = mGeneralDataBaseTemplate
						.getSQLiteStatement(fileSql.toString());

				for (MediaFile mediaFile : fileList) {
					fileStatement.bindString(1, mediaFile.getMediaFileName());
					fileStatement.bindString(2,
							mediaFile.getMediaFileNameSuffix());
					fileStatement.bindString(3, mediaFile.getMediaFilePath());
					fileStatement.bindString(4,
							mediaFile.getMediaFileParentPath());
					fileStatement.bindLong(5,
							mediaFile.getMediaFileDateModified());
					fileStatement.bindString(6,
							mediaFile.getMediaFileArtistName());
					// 文件
					fileStatement.bindLong(7, 0);
					fileStatement.bindString(8,
							mediaFile.getMediaFileAlbumName());
					fileStatement.bindLong(9,
							mediaFile.getMediaFileDateDuration());
					// 是否加密
					fileStatement.bindLong(10,
							mediaFile.getMediaFileIsEncrypt() ? 1 : 0);
					fileStatement.bindLong(11, mediaFile.getMediaFileSize());
					fileStatement.bindLong(12, System.currentTimeMillis());

					mGeneralDataBaseTemplate.insert(fileStatement);
				}

				mGeneralDataBaseTemplate.setTransactionSuccessful();

			} catch (Exception e) {
				e.printStackTrace();
				L.v(TAG, e.getMessage());
				return false;
			} finally {
				mGeneralDataBaseTemplate.endTransaction();
				mGeneralDataBaseTemplate.close();
			}
			return true;
		}
	}

	/**
	 * 
	 * 删除文件列表
	 * 
	 * @param fileList
	 *            文件集合
	 * @return int 操作状态
	 */
	public int deleteFileList(List<String> pathList) {
		synchronized (TAG) {

			try {

				mGeneralDataBaseTemplate.open();

				mGeneralDataBaseTemplate.beginTransaction();

				// 创建sql语句
				for (String path : pathList) {

					// 删除数据库
					StringBuilder where = new StringBuilder();
					where.append(DIRECTORY_PATH + "=?");
					mGeneralDataBaseTemplate.delete(TB_DIRECTORY_TREE,
							where.toString(), new String[] { path });
					// 删除媒体库
					sContext.getContentResolver().delete(
							MediaStore.Video.Media.EXTERNAL_CONTENT_URI,
							MediaStore.Video.Media.DATA + "=?",
							new String[] { path });

					// 删除文件
					File deleteFile = new File(path);
					if (deleteFile.exists()) {
						deleteFile.delete();
					}
				}

				mGeneralDataBaseTemplate.setTransactionSuccessful();

			} catch (Exception e) {
				e.printStackTrace();
				L.v(TAG, e.getMessage());
				return -1;
			} finally {
				mGeneralDataBaseTemplate.endTransaction();
				mGeneralDataBaseTemplate.close();
			}
			return 1;
		}
	}

	/**
	 * 
	 * 更新文件列表
	 * 
	 * @param fileList
	 *            文件集合
	 * @return boolean 是否成功
	 */
	public boolean updateFileList(List<MediaFile> fileList) {
		synchronized (TAG) {

			try {

				mGeneralDataBaseTemplate.open();

				mGeneralDataBaseTemplate.beginTransaction();

				// 文件

				for (MediaFile mediaFile : fileList) {

					// 创建文件sql语句
					ContentValues contentValues = new ContentValues();
					contentValues.put(DIRECTORY_AUTHOR_NAME,
							mediaFile.getMediaFileArtistName());
					contentValues.put(FILE_ALBUM_NAME,
							mediaFile.getMediaFileAlbumName());
					contentValues.put(FILE_DATE_DURATION,
							mediaFile.getMediaFileDateDuration());
					contentValues.put(FILE_SIZE, mediaFile.getMediaFileSize());
					StringBuilder where = new StringBuilder();
					where.append(DIRECTORY_PATH).append("=?");
					mGeneralDataBaseTemplate.update(TB_DIRECTORY_TREE,
							contentValues, where.toString(),
							new String[] { mediaFile.getMediaFilePath() });
				}

				mGeneralDataBaseTemplate.setTransactionSuccessful();

			} catch (Exception e) {
				e.printStackTrace();
				L.v(TAG, e.getMessage());
				return false;
			} finally {
				mGeneralDataBaseTemplate.endTransaction();
				mGeneralDataBaseTemplate.close();
			}
			return true;
		}
	}

	/**
	 * 
	 * 根据文件路径删除文件 <br>
	 * 同步方法
	 * 
	 * @param filePath
	 *            文件路径
	 * @return int 操作结果<br>
	 *         1 成功<br>
	 *         -1 删除异常<br>
	 */
	public int deleteFileByFilePath(String filePath) {
		List<String> deleteList = new ArrayList<String>();
		deleteList.add(filePath);
		return deleteFileList(deleteList);
	}

	public int getFileCount() {
		String sql = "select *  from " + TB_DIRECTORY_TREE + " where "
				+ DIRECTORY_TYPE + "=0";
		try {

			mGeneralDataBaseTemplate.open();

			Cursor cursor = mGeneralDataBaseTemplate.select(sql);
			if (cursor != null) {
				int num = cursor.getCount();
				cursor.close();
				return num;
			}

		} catch (Exception e) {
		} finally {
			mGeneralDataBaseTemplate.open();
		}

		return 0;
	}

	/**
	 * 
	 * 获得加密或未加密文件集合
	 * 
	 * @return ArrayList<MediaFile> 文件集合
	 */
	public ArrayList<MediaFile> getFileList(int listType) {
		StringBuilder sql = new StringBuilder("select * from "
				+ TB_DIRECTORY_TREE + " where " + DIRECTORY_TYPE + "=0 and ");
		switch (listType) {
		case StorageModule.LIST_TYPE_FILE_NORMAL_VIDEO:
			// 未加密
			sql.append(FILE_IS_ENCRYPT + "=0");
			break;
		case StorageModule.LIST_TYPE_FILE_ENCRYPT_VIDEO:
			// 加密
			sql.append(FILE_IS_ENCRYPT + "=1");
			break;
		default:
			return null;
		}

		// 读取数据
		ArrayList<MediaFile> mediaFileList = null;
		Cursor cursor = null;
		try {

			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}
			mediaFileList = new ArrayList<MediaFile>();
			while (cursor.moveToNext()) {
				MediaFile mediaFile = new MediaFile();
				mediaFile.setMediaFileId(cursor.getLong(cursor
						.getColumnIndex(DIRECTORY_ID)));
				mediaFile.setMediaFileName(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_NAME)));
				mediaFile.setMediaFileNameSuffix(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_NAME_SUFFIX)));
				mediaFile.setMediaFilePath(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_PATH)));
				mediaFile.setMediaFileParentPath(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_PARENT_PATH)));
				mediaFile.setMediaFileDateModified(cursor.getLong(cursor
						.getColumnIndex(DIRECTORY_DATE_MODIFIED)));
				mediaFile.setMediaFileArtistName(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_AUTHOR_NAME)));
				mediaFile.setMediaFileAlbumName(cursor.getString(cursor
						.getColumnIndex(FILE_ALBUM_NAME)));
				mediaFile.setMediaFileDateDuration(cursor.getLong(cursor
						.getColumnIndex(FILE_DATE_DURATION)));
				mediaFile.setMediaFileSize(cursor.getLong(cursor
						.getColumnIndex(FILE_SIZE)));
				mediaFile.setMediaFileIsEncrypt(cursor.getInt(cursor
						.getColumnIndex(FILE_IS_ENCRYPT)) == 1);
				mediaFile.setMediaFileEncryptTime(cursor.getLong(cursor
						.getColumnIndex(FILE_ENCRYPT_TIME)));
				mediaFileList.add(mediaFile);
			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return mediaFileList;
	}

	/**
	 * 
	 * 根据文件夹路径获得文件集合
	 * 
	 * @return ArrayList<MediaFile> 文件集合
	 */
	public ArrayList<MediaFile> getFileList(String filePath) {
		StringBuilder sql = new StringBuilder("select * from "
				+ TB_DIRECTORY_TREE + " where ");
		sql.append(DIRECTORY_PARENT_PATH + "='" + filePath + "'");
		sql.append(" and ");
		sql.append(DIRECTORY_TYPE + "=0");
		sql.append(" and ");
		sql.append(FILE_IS_ENCRYPT + "=0");
		ArrayList<MediaFile> mediaFileList = null;
		Cursor cursor = null;
		try {

			mGeneralDataBaseTemplate.open();

			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			mediaFileList = new ArrayList<MediaFile>();
			while (cursor.moveToNext()) {
				MediaFile mediaFile = new MediaFile();
				mediaFile.setMediaFileId(cursor.getLong(cursor
						.getColumnIndex(DIRECTORY_ID)));
				mediaFile.setMediaFileName(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_NAME)));
				mediaFile.setMediaFileNameSuffix(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_NAME_SUFFIX)));
				mediaFile.setMediaFilePath(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_PATH)));
				mediaFile.setMediaFileParentPath(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_PARENT_PATH)));
				mediaFile.setMediaFileDateModified(cursor.getLong(cursor
						.getColumnIndex(DIRECTORY_DATE_MODIFIED)));
				mediaFile.setMediaFileArtistName(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_AUTHOR_NAME)));
				mediaFile.setMediaFileAlbumName(cursor.getString(cursor
						.getColumnIndex(FILE_ALBUM_NAME)));
				mediaFile.setMediaFileDateDuration(cursor.getLong(cursor
						.getColumnIndex(FILE_DATE_DURATION)));
				mediaFile.setMediaFileSize(cursor.getLong(cursor
						.getColumnIndex(FILE_SIZE)));
				mediaFile.setMediaFileIsEncrypt(cursor.getInt(cursor
						.getColumnIndex(FILE_IS_ENCRYPT)) == 1);
				mediaFile.setMediaFileEncryptTime(cursor.getLong(cursor
						.getColumnIndex(FILE_ENCRYPT_TIME)));
				mediaFileList.add(mediaFile);
			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}

		return mediaFileList;
	}

	/**
	 * 
	 * 获得文件夹集合
	 * 
	 * @return ArrayList<MediaFile> 文件夹集合
	 */
	public ArrayList<MediaFolder> getFolderList() {
		StringBuilder sqlForder = new StringBuilder("select * from "
				+ TB_DIRECTORY_TREE + " where " + DIRECTORY_TYPE + "=1");
		ArrayList<MediaFolder> mediaFolderList = null;
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();

			cursor = mGeneralDataBaseTemplate.select(sqlForder.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			mediaFolderList = new ArrayList<MediaFolder>();
			while (cursor.moveToNext()) {
				MediaFolder mediaFolder = new MediaFolder();
				mediaFolder.setMediaFolderName(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_NAME)));
				String folderPath = cursor.getString(cursor
						.getColumnIndex(DIRECTORY_PATH));
				mediaFolder.setMediaFolderPath(folderPath);

				mediaFolder.setMediaFolderParentPath(cursor.getString(cursor
						.getColumnIndex(DIRECTORY_PARENT_PATH)));

				// 查询该路径下的文件数量
				StringBuilder sqlFile = new StringBuilder(
						"select count(*) from " + TB_DIRECTORY_TREE + " where ");
				sqlFile.append(DIRECTORY_TYPE + "=0 and ");
				sqlFile.append(FILE_IS_ENCRYPT + "=0 and ");
				sqlFile.append(DIRECTORY_PARENT_PATH + "='" + folderPath + "'");
				Cursor fileCursor = mGeneralDataBaseTemplate.select(sqlFile
						.toString());
				if (fileCursor != null && fileCursor.moveToNext()) {

					int num = fileCursor.getInt(0);

					// 过滤空文件夹
					if (num > 0) {

						mediaFolder.setMediaFolderTotalNum(num);
						mediaFolderList.add(mediaFolder);

					}

					fileCursor.close();
				}

			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}

		return mediaFolderList;
	}

	/**
	 * 更新文件信息
	 * 
	 * @param filePath
	 *            文件路径
	 * @return int 更新状态
	 */
	int updateFileByFilePath(String filePath) {
		int result = 1;
		try {
			File file = new File(filePath);
			if (!file.exists()) {
				return -2;
			}

			String fileName = file.getName();
			int dotIndex = fileName.lastIndexOf(".");
			if (dotIndex == -1) {
				return -3;
			}
			StringBuilder sql = new StringBuilder("update ");
			sql.append(TB_DIRECTORY_TREE);
			sql.append(" set ");
			sql.append(DIRECTORY_DATE_MODIFIED);
			sql.append("=");
			sql.append(file.lastModified());
			sql.append(" and ");
			sql.append(DIRECTORY_NAME);
			sql.append("=");
			sql.append(fileName.substring(0, dotIndex));
			sql.append(" and ");
			sql.append(DIRECTORY_NAME_SUFFIX);
			sql.append("=");
			sql.append(fileName);
			sql.append(" and ");
			sql.append(FILE_SIZE);
			sql.append("=");
			sql.append(file.length());
			sql.append(" where ");
			sql.append(DIRECTORY_PATH);
			sql.append("='");
			sql.append(filePath);
			sql.append("'");
			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.update(sql.toString());

		} catch (Exception e) {
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 更新加密状态
	 * 
	 * @param fileId
	 *            文件ID
	 * @param isEncrypt
	 *            是否加密
	 * @return int
	 */
	int updateEncryptState(long fileId, boolean isEncrypt) {
		int result = 1;
		try {

			StringBuilder sql = new StringBuilder();
			sql.append("update ");
			sql.append(TB_DIRECTORY_TREE);
			sql.append(" set ");
			sql.append(FILE_IS_ENCRYPT).append("=").append(isEncrypt ? 1 : 0)
					.append(",");
			sql.append(FILE_ENCRYPT_TIME).append("=")
					.append(System.currentTimeMillis());
			sql.append(" where ");
			sql.append(DIRECTORY_ID).append("=").append(fileId);
			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.update(sql.toString());

		} catch (Exception e) {
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}
}
